package frame.usermeau.service.serviceimpl;

import frame.model.SnowflakeIdWorker;
import frame.usermeau.service.ServiceFrame;
import tech.tablesaw.api.StringColumn;
import tech.tablesaw.api.Table;

import javax.swing.*;
import java.awt.*;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class InsuranceServiceFrame extends ServiceFrame {

    // 用户ID
    private String userId;

    public InsuranceServiceFrame(String userId) {
        super();
        this.userId = userId;
    }

    /**
     * 初始化搜索结果(显示所有结果)
     * @param jTextArea
     */
    public void padding(JTextArea jTextArea) {
        init();
        Table table = null;

        List<String> insureIDList = new ArrayList<>();
        List<String> insureNameList = new ArrayList<>();
        List<String> insurePriceList = new ArrayList<>();
        List<String> insureAgeList = new ArrayList<>();
        List<String> insureWordList = new ArrayList<>();
        try {
            PreparedStatement preparedStatement = conn.prepareStatement("select in_id, in_name, in_price, in_people_lower, in_people_upper,in_coverage,in_is_on_sale from t_insurance");
            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                String insureId = resultSet.getString(1);
                String insureName = resultSet.getString(2);
                String insurePrice = resultSet.getString(3);
                String insureAge = resultSet.getString(4) + "岁 ~ " +resultSet.getString(5) + "岁";
                String insureWord = resultSet.getString(6);
                boolean isOnSale = resultSet.getBoolean(7);
                if (isOnSale) {
                    insureIDList.add(insureId);
                    insureNameList.add(insureName);
                    insurePriceList.add(insurePrice);
                    insureAgeList.add(insureAge);
                    insureWordList.add(insureWord);
                }
            }
            table = Table.create("相关保险").addColumns(
                    StringColumn.create("保险ID",insureIDList),
                    StringColumn.create("保险名称",insureNameList),
                    StringColumn.create("保险价格",insurePriceList),
                    StringColumn.create("保险年龄范围",insureAgeList),
                    StringColumn.create("保险描述",insureWordList)
            );
            jTextArea.setText(table.print());
            resultSet.close();
            preparedStatement.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    /**
     * 显示导航栏，固定不改变
     * @return
     */
    @Override
    public JPanel showOptions() {
        JPanel jPanel = new JPanel(null);

        JLabel jl;
        //年龄
        jl = new JLabel("年龄:");
        jl.setSize(80,20);
        jl.setLocation(30,20);
        jPanel.add(jl);
        JTextArea inputAge = new JTextArea();
        inputAge.setSize(100,20);
        inputAge.setLocation(100,20);
        jPanel.add(inputAge);
        jl = new JLabel("岁");
        jl.setSize(80,20);
        jl.setLocation(200,20);
        jPanel.add(jl);

        //名称
        jl = new JLabel("名称:");
        jl.setSize(80,20);
        jl.setLocation(250,20);
        jPanel.add(jl);
        JTextArea inputName = new JTextArea();
        inputName.setSize(150,20);
        inputName.setLocation(300,20);
        jPanel.add(inputName);

        //确认按钮
        JButton jButton = new JButton("确认");
        jButton.setSize(60,40);
        jButton.setLocation(300,50);
        jPanel.add(jButton);

        //结果框
        JTextArea result = new JTextArea();
        result.setSize(450,500);
        result.setLocation(30,100);
        jPanel.add(result);
        padding(result);

        jButton.addMouseListener(new MouseAdapter() {
            @Override
            public void mouseClicked(MouseEvent e) {
                String age = inputAge.getText();
                String name = inputName.getText();
                Table strs;
                if (cal(age)) {
                    strs = showMessage(age, name);
                    result.setText("");
                    result.setText(strs.print());
                }else {
                    JOptionPane.showMessageDialog(null, "年龄请输入数字");
                }
            }
        });
        return jPanel;
    }

    /**
     * 返回结果
     * @param age   年龄
     * @param name 名称
     */
    private Table showMessage(String age, String name) {
        age = age.trim();
        name = name.trim();

        init();
        Table table = null;
        List<String> insureIDList = new ArrayList<>();
        List<String> insureNameList = new ArrayList<>();
        List<String> insurePriceList = new ArrayList<>();
        List<String> insureAgeList = new ArrayList<>();
        List<String> insureWordList = new ArrayList<>();
        if (!"".equals(age)) {
            try {
                PreparedStatement selectInsure = conn.prepareStatement("select in_id, in_name, in_price, in_people_lower, in_people_upper,in_coverage,in_is_on_sale from t_insurance where in_name like '%'||?||'%' and in_young_people <= ? and in_old_people >= ?");
                selectInsure.setString(1,name);
                selectInsure.setString(2,age);
                selectInsure.setString(3,age);
                ResultSet resultSet = selectInsure.executeQuery();
                while (resultSet.next()) {
                    String insureId = resultSet.getString(1);
                    String insureName = resultSet.getString(2);
                    String insurePrice = resultSet.getString(3);
                    String insureAge = resultSet.getString(4) + "岁 ~ " +resultSet.getString(5) + "岁";
                    String insureWord = resultSet.getString(6);
                    boolean isOnSale = resultSet.getBoolean(7);
                    if (isOnSale) {
                        insureIDList.add(insureId);
                        insureNameList.add(insureName);
                        insurePriceList.add(insurePrice);
                        insureAgeList.add(insureAge);
                        insureWordList.add(insureWord);
                    }
                }
                table = Table.create("相关保险").addColumns(
                        StringColumn.create("保险ID",insureIDList),
                        StringColumn.create("保险名称",insureNameList),
                        StringColumn.create("保险价格",insurePriceList),
                        StringColumn.create("保险年龄范围",insureAgeList),
                        StringColumn.create("保险描述",insureWordList)
                );
                resultSet.close();
                selectInsure.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }else {
            try {
                PreparedStatement preparedStatement = conn.prepareStatement("select in_id, in_name, in_price, in_people_lower, in_people_upper,in_coverage,in_is_on_sale from t_insurance where in_name like '%'||?||'%'");
                preparedStatement.setString(1,name);
                ResultSet resultSet = preparedStatement.executeQuery();
                while (resultSet.next()) {
                    String insureId = resultSet.getString(1);
                    String insureName = resultSet.getString(2);
                    String insurePrice = resultSet.getString(3);
                    String insureAge = resultSet.getString(4) + "岁 ~ " +resultSet.getString(5) + "岁";
                    String insureWord = resultSet.getString(6);
                    boolean isOnSale = resultSet.getBoolean(7);
                    if (isOnSale) {
                        insureIDList.add(insureId);
                        insureNameList.add(insureName);
                        insurePriceList.add(insurePrice);
                        insureAgeList.add(insureAge);
                        insureWordList.add(insureWord);
                    }
                }
                table = Table.create("相关保险").addColumns(
                        StringColumn.create("保险ID",insureIDList),
                        StringColumn.create("保险名称",insureNameList),
                        StringColumn.create("保险价格",insurePriceList),
                        StringColumn.create("保险年龄范围",insureAgeList),
                        StringColumn.create("保险描述",insureWordList)
                );
                resultSet.close();
                preparedStatement.close();
                conn.close();

            } catch (SQLException e) {
                e.printStackTrace();
            }


        }
        return table;
    }


    /**
     * 购买产品
     * @param name      产品名称
     * @param number    购买数目
     */
    @Override
    public void purchase(String name, Integer number) {
        name = name.trim();
        if (isTrueFund(name)) {
            // 保险ID输入无误

            BigDecimal limit = new BigDecimal(-1000);

            // 卡号和卡里面的钱
            String useCard = null;
            BigDecimal moneyInCard = null;

            // 1. 查询ID为name的保险对应的价格为多少
            double price = getPriceByInsureID(name);

            // 2. 验证是否有卡内的余额超过 价格x数量, 如果有记录下将要使用的卡号
            BigDecimal amount = new BigDecimal(price * number);

            init();
            PreparedStatement getAllCards = null;
            try {
                getAllCards = conn.prepareStatement("select c_id, c_type, c_balance from t_card where c_user_id = ?");
                getAllCards.setString(1, userId);
                ResultSet resultSet = getAllCards.executeQuery();
                while (resultSet.next()) {
                    String cardId = resultSet.getString(1);
                    int typeCard = resultSet.getInt(2);
                    BigDecimal cardMoney = resultSet.getBigDecimal(3);
                    if (typeCard == 0) {
                        // 储蓄卡
                        if (cardMoney.compareTo(amount) >= 0) {
                            useCard = cardId;
                            moneyInCard = cardMoney;
                            break;
                        }
                    }else {
                        // 信用卡
                        if (cardMoney.subtract(amount).compareTo(limit) >= 0) {
                            useCard = cardId;
                            moneyInCard = cardMoney;
                            break;
                        }
                    }
                }
                conn.close();
                getAllCards.close();
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }


            // 3. 事务:
            //   3.1 扣钱
            //   3.2 增加保险财产
            //   3.2 增加消费记录
            if (useCard == null) {
                JOptionPane.showMessageDialog(null, "账户金额不足！");
            }else{
                init();
                try {
                    conn.setAutoCommit(false);

                    PreparedStatement updateCard = conn.prepareStatement("update t_card set c_balance = c_balance - ? where c_id = ? ");
                    PreparedStatement insertInsureAsserts = conn.prepareStatement("insert into t_insassets(ina_id, ina_status, ina_amount, ina_income, ina_date, ina_user_id, ina_insurance_id) values(?,?,?,?,now(),?,?)");
                    PreparedStatement insertTransaction = conn.prepareStatement("insert into t_transaction(tra_id, tra_amount, tra_time, tra_card_id) values(?,?,now(),?)");

                    updateCard.setDouble(1, amount.doubleValue());
                    updateCard.setString(2, useCard);

                    insertInsureAsserts.setString(1, idWorker.nextId() + "44");
                    insertInsureAsserts.setInt(2, 1);
                    insertInsureAsserts.setDouble(3, amount.doubleValue());
                    insertInsureAsserts.setInt(4, 0);
                    insertInsureAsserts.setString(5, userId);
                    insertInsureAsserts.setString(6, name);

                    insertTransaction.setString(1, idWorker.nextId() + "88");
                    insertTransaction.setDouble(2, amount.doubleValue() * (-1));
                    insertTransaction.setString(3, useCard);

                    updateCard.executeUpdate();
                    insertInsureAsserts.execute();
                    insertTransaction.execute();

                    conn.commit();
                    updateCard.close();
                    insertInsureAsserts.close();
                    insertTransaction.close();
                    conn.close();
                    this.dispose();
                } catch (SQLException e) {
                    try {
                        conn.rollback();
                    } catch (SQLException ex) {
                        ex.printStackTrace();
                    }
                    e.printStackTrace();
                }
            }
        }
    }

    public double getPriceByInsureID(String insureId) {
        init();
        try {
            PreparedStatement preparedStatement = conn.prepareStatement("select in_price from t_insurance where in_id = ?");
            preparedStatement.setString(1, insureId);

            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {

                double aDouble = resultSet.getDouble(1);

                preparedStatement.close();
                resultSet.close();
                conn.close();
                return aDouble;
            }
            preparedStatement.close();
            resultSet.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }



    public boolean isTrueFund(String inId) {
        if (inId.length() != 20) {
            JOptionPane.showMessageDialog(null, "保险ID输入格式不对，请重新输入");
            return false;
        }else{
            init();
            try {
                PreparedStatement preparedStatement = conn.prepareStatement("select in_id from t_insurance where in_id = ?");
                preparedStatement.setString(1,inId);
                ResultSet resultSet = preparedStatement.executeQuery();
                if (resultSet.next()) {
                    return true;
                }
                conn.close();
                resultSet.close();
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            JOptionPane.showMessageDialog(null, "没有以此为ID的基金");
            return false;
        }
    }

    Connection conn;
    SnowflakeIdWorker idWorker;
    public void init() {
        try {
            Class.forName("org.opengauss.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            conn = DriverManager.getConnection("jdbc:opengauss://121.36.41.207:26000/finance", "geqiyu", "Tlyz0729");

        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            // 设置demo为搜索路径
            PreparedStatement firstStep = conn.prepareStatement("SET SEARCH_PATH TO db1,public");
            firstStep.execute();
            firstStep.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        idWorker = new SnowflakeIdWorker(0, 0);
    }

    public static void main(String[] args) {

    }
}
